"""
Case Type   : 序列
Case Name   : 不设置cache大批量插入数据，对比设置cache大批量插入数据耗时
Create At   : 2024/4/15
Owner       : @li-xin12345
Description :
    1、创建序列、创建表
    2、查看cache值
    3、不设置cache大批量插入数据，记录查询耗时
    4、设置cache大批量插入数据，记录查询耗时
    5、对比两种场景的查询耗时
    6、清理环境
Expect      :
    1、成功
    2、查询cache值为默认值1
    3、成功
    4、成功
    5、不设置cache的查询耗时大于设置
    6、成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class SequenceTest(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.seq_name_01 = 's_sequence_0014_01'
        self.seq_name_02 = 's_sequence_0014_02'
        self.tb_name_01 = 't_sequence_0014_01'
        self.tb_name_02 = 't_sequence_0014_02'

    def test_sequence(self):
        text = '-----step1:创建序列、创建表;expect:成功-----'
        self.log.info(text)
        sql = f'''drop sequence if exists {self.seq_name_01} cascade;
        drop sequence if exists {self.seq_name_02} cascade;
        drop table if exists {self.tb_name_01} cascade;
        drop table if exists {self.tb_name_02} cascade;
        create sequence {self.seq_name_01};
        create sequence {self.seq_name_02};
        create table {self.tb_name_01}(
        id int default nextval('{self.seq_name_01}'), num int);
        create table {self.tb_name_02}(
        id int default nextval('{self.seq_name_02}'), num int);
        '''
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_SEQUENCE_SUCCESS_MSG, res,
                      '执行失败：' + text)
        self.assertIn(self.constant.CREATE_SEQUENCE_SUCCESS_MSG, res,
                      '执行失败：' + text)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, res,
                      '执行失败：' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, res,
                      '执行失败：' + text)
        self.assertEqual(res.count(self.constant.DROP_SEQUENCE_SUCCESS_MSG),
                         2, '执行失败：' + text)
        self.assertEqual(res.count(self.constant.CREATE_SEQUENCE_SUCCESS_MSG),
                         2, '执行失败：' + text)
        self.assertEqual(res.count(self.constant.DROP_TABLE_SUCCESS),
                         2, '执行失败：' + text)
        self.assertEqual(res.count(self.constant.CREATE_TABLE_SUCCESS),
                         2, '执行失败：' + text)

        text = '-----step2:查看cache值;expect:查询cache值为默认值1-----'
        self.log.info(text)
        sql = f'select last_value, cache_value from {self.seq_name_01};' \
              f'select last_value, cache_value from {self.seq_name_02};'
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertEqual(res.count('1'), 6, '执行失败：' + text)
        self.assertNotIn('ERROR', res, '执行失败：' + text)

        text = '-----step3:不设置cache大批量插入数据，记录查询耗时;expect:成功-----'
        self.log.info(text)
        sql = f'explain performance insert into {self.tb_name_01}(num)' \
              f'(select * from generate_series(1, 1000000));'
        res = self.pri_sh.execut_db_sql(sql).splitlines()[-2].split('|')
        self.log.info(res)
        self.assertNotIn('ERROR', res, '执行失败：' + text)
        self.assertIn('Total runtime', res[0], '执行失败：' + text)
        # without_cache_time = res.splitlines()[-2].split(':')[-1].split()[
        #     0].strip()
        without_cache_time = res[0].split(':')[-1].split()[0].strip()
        self.log.info(without_cache_time)

        text = '-----step4:设置cache大批量插入数据，记录查询耗时;expect:成功-----'
        self.log.info(text)
        sql = f'alter sequence {self.seq_name_02} cache 1000000;' \
              f'explain performance insert into {self.tb_name_02}(num)' \
              f'(select * from generate_series(1, 1000000));'
        alter_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(alter_res)
        self.assertNotIn('ERROR', alter_res, '执行失败：' + text)
        self.assertIn('Total runtime', alter_res, '执行失败：' + text)

        time_res = self.pri_sh.execut_db_sql(sql).splitlines()[-2].split('|')
        self.log.info(res)
        with_cache_time = time_res[0].split(':')[-1].split()[0].strip()
        self.log.info(with_cache_time)

        text = '-----step5:对比两种场景的查询耗时;expect:不设置cache的查询耗时大于设置-----'
        self.log.info(text)
        self.assertGreater(without_cache_time, with_cache_time,
                           '执行失败：' + text)

    def tearDown(self):
        text = '-----step6:清理环境;expect:成功-----'
        self.log.info(text)
        sql = f'''drop sequence if exists {self.seq_name_01} cascade;
        drop sequence if exists {self.seq_name_02} cascade;
        drop table if exists {self.tb_name_01} cascade;
        drop table if exists {self.tb_name_02} cascade;
        '''
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_SEQUENCE_SUCCESS_MSG, res,
                      '执行失败：' + text)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, res,
                      '执行失败：' + text)
        self.assertEqual(res.count(self.constant.DROP_SEQUENCE_SUCCESS_MSG),
                         2, '执行失败：' + text)
        self.assertEqual(res.count(self.constant.DROP_TABLE_SUCCESS),
                         2, '执行失败：' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
